# -*- coding: utf-8 -*-
import uuid
import os
from mysql.connector import connection, errorcode
import xml.etree.cElementTree as ET


config = {
    "host": os.getenv("MYSQL_HOST", "127.0.0.1"),
    "password": os.getenv("MYSQL_PASSWORD", "root"),
    "user": os.getenv("MYSQL_USER", "root"),
    "database": os.getenv("MYSQL_DATABASE", "")
}

if not config["database"]:
    print("required 'database' for mysql")
    exit(1)

try:
    cnx = connection.MySQLConnection(**config)
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
    exit(1)

# Get two buffered cursors
curA = cnx.cursor(buffered=True)
curB = cnx.cursor(buffered=True)
curC = cnx.cursor(buffered=True)

insert_stations = "INSERT tb_stations ( stat_id, `name`, is_terminal, `status` ) VALUES( %s, %s, %s, %s )"
insert_lines = "INSERT tb_lines ( line_id, `name` ) VALUES( %s, %s)"
insert_station_line = "INSERT tb_station_line ( stat_id, line_id ) VALUES( %s, %s)"
exist_lines = {}
tree = ET.ElementTree(file='stations.xml')
root = tree.getroot()
for elem in tree.iter():
    if elem.attrib.get("name") is None or elem.attrib.get("linename") is None:
        continue
    # lines
    linenames = elem.attrib.get("linename").split("||||||")
    for linename in linenames:
        if not linename or linename in exist_lines.keys():
            continue
        line_id = str(uuid.uuid4())
        curB.execute(insert_lines, (line_id, linename))
        exist_lines[linename] = line_id

    # stations
    stat_id = str(uuid.uuid4())
    curA.execute(insert_stations, (stat_id, elem.attrib.get("name"), 0, 1))
    # station_line
    for linename in linenames:
        curC.execute(insert_station_line, (stat_id, exist_lines[linename]))
    cnx.commit()

cnx.close()
